/*
 * Copyright (C) 2012 Santiago Valdarrama
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at 
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * 
 */
package com.yuanmei.stockeyes.data;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;

//import android.util.Log;

import com.yuanmei.stockeyes.model.Stock;

public class LonghornDatabase {

    public final static String DATABASE_NAME = "longhorn";
    public final static String TAG = "LonghornDatabase";

    // private final static String TABLE_EXCHANGE = "exchange";
    private final static String TABLE_WATCHLIST = "watchlist";
    private final static String TABLE_STOCK = "stock";
    private final static String TABLE_STOCK_TMDATA = "stock_timedata";

    public final static String KEY_ROWID = "rowid";

    public final static String KEY_WATCHLIST_NAME = "watchlist_name";
    public final static String KEY_WATCHLIST_CODE = "watchlist_code";
    public final static String KEY_WATCHLIST_ORDER = "watchlist_order";

    public final static String KEY_STOCK_CODE = "stock_code";
    public final static String KEY_STOCK_NAME = "stock_name";
    public final static String KEY_STOCK_CHANGE_PERCENTAGE = "stock_change_percentage"; // netease-126,jarson
                                                                                        // "percent"
    public final static String KEY_STOCK_CHANGE = "stock_change"; // netease-126,jarson
                                                                  // "updown"
    public final static String KEY_STOCK_PRICE = "stock_price";
    public final static String KEY_STOCK_OPEN = "stock_open";
    public final static String KEY_STOCK_LOW = "stock_low";
    public final static String KEY_STOCK_HIGH = "stock_high";
    public final static String KEY_STOCK_YESTCLOSE = "stock_yestclose";
    public final static String KEY_STOCK_VOLUME = "stock_volume";
    public final static String KEY_STOCK_TURNOVER = "stock_turnover"; // ����
    public final static String KEY_STOCK_BID5 = "stock_bid5";
    public final static String KEY_STOCK_BIDVOL5 = "stock_bidvol5";
    public final static String KEY_STOCK_BID4 = "stock_bid4";
    public final static String KEY_STOCK_BIDVOL4 = "stock_bidvol4";
    public final static String KEY_STOCK_BID3 = "stock_bid3";
    public final static String KEY_STOCK_BIDVOL3 = "stock_bidvol3";

    public final static String KEY_STOCK_BID2 = "stock_bid2";
    public final static String KEY_STOCK_BIDVOL2 = "stock_bidvol2";
    public final static String KEY_STOCK_BID1 = "stock_bid1";
    public final static String KEY_STOCK_BIDVOL1 = "stock_bidvol1";
    public final static String KEY_STOCK_ASK1 = "stock_ask1";
    public final static String KEY_STOCK_ASKVOL1 = "stock_askvol1";
    public final static String KEY_STOCK_ASK2 = "stock_ask2";
    public final static String KEY_STOCK_ASKVOL2 = "stock_askvol2";
    public final static String KEY_STOCK_ASK3 = "stock_ask3";
    public final static String KEY_STOCK_ASKVOL3 = "stock_askvol3";
    public final static String KEY_STOCK_ASK4 = "stock_ask4";
    public final static String KEY_STOCK_ASKVOL4 = "stock_askvol4";
    public final static String KEY_STOCK_ASK5 = "stock_ask5";
    public final static String KEY_STOCK_ASKVOL5 = "stock_askvol5";
    public final static String KEY_STOCK_UPDATE = "stock_update";

    // TABLE stock_timedata
    public final static String KEY_STOCK_TMDATA_REC_CODE = "rec_code";
    public final static String KEY_STOCK_TMDATA_REC_TIME = "rec_time";
    public final static String KEY_STOCK_TMDATA_REC_PRICE = "rec_price";
    public final static String KEY_STOCK_TMDATA_REC_VOL = "rec_vol";
    public final static String KEY_STOCK_TMDATA_REC_TURNOVER = "rec_turnover";

    // TABLE stock_chart
    public final static String KEY_STOCK_CHART_CODE = "code";
    public final static String KEY_STOCK_CHART_UPDATE_TIME = "update_time";
    public final static String KEY_STOCK_CHART_TIMECHART_PATH = "timechart_path";
    public final static String KEY_STOCK_CHART_DAYCHART_PATH = "daychart_path";
    public final static String KEY_STOCK_CHART_WEEKCHART_PATH = "weekchart_path";
    public final static String KEY_STOCK_CHART_MONTHCHART_PATH = "monthchart_path";

    // public static final String KEY_EXCHANGE_SYMBOL = "exchange_symbol";
    // public static final String KEY_EXCHANGE_NAME = "exchange_name";    

    private LonghornOpenHelper longhornOpenHelper;

    public LonghornDatabase(Context context) {
        this.longhornOpenHelper = new LonghornOpenHelper(context);
    }

    public Cursor getWatchList() {
        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();

        builder.setTables(TABLE_WATCHLIST + " wl INNER JOIN " + TABLE_STOCK
                + " sd ON wl." + KEY_WATCHLIST_CODE + "= sd." + KEY_STOCK_CODE);
        String[] columns = { KEY_STOCK_CODE, KEY_STOCK_NAME,
                KEY_STOCK_CHANGE_PERCENTAGE, KEY_STOCK_CHANGE, KEY_STOCK_PRICE,
                KEY_STOCK_OPEN, KEY_STOCK_LOW, KEY_STOCK_HIGH,
                KEY_STOCK_YESTCLOSE, KEY_STOCK_VOLUME, KEY_STOCK_TURNOVER,
                KEY_STOCK_BID5, KEY_STOCK_BIDVOL5, KEY_STOCK_BID4,
                KEY_STOCK_BIDVOL4, KEY_STOCK_BID3, KEY_STOCK_BIDVOL3,
                KEY_STOCK_BID2, KEY_STOCK_BIDVOL2, KEY_STOCK_BID1,
                KEY_STOCK_BIDVOL1, KEY_STOCK_ASK1, KEY_STOCK_ASKVOL1,
                KEY_STOCK_ASK2, KEY_STOCK_ASKVOL2, KEY_STOCK_ASK3,
                KEY_STOCK_ASKVOL3, KEY_STOCK_ASK4, KEY_STOCK_ASKVOL4,
                KEY_STOCK_ASK5, KEY_STOCK_ASKVOL5, KEY_STOCK_UPDATE };

        return builder.query(getReadableDatabase(), columns, "", new String[0],
                null, null, KEY_WATCHLIST_ORDER);
    }

    public Cursor getStock(String symbol) {
        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();

        builder.setTables(TABLE_STOCK);
        String[] columns = { KEY_STOCK_CODE, KEY_STOCK_NAME,
                KEY_STOCK_CHANGE_PERCENTAGE, KEY_STOCK_CHANGE, KEY_STOCK_PRICE,
                KEY_STOCK_VOLUME, KEY_STOCK_YESTCLOSE, KEY_STOCK_LOW,
                KEY_STOCK_HIGH, KEY_STOCK_TURNOVER, KEY_STOCK_BID5,
                KEY_STOCK_BIDVOL5, KEY_STOCK_BID4, KEY_STOCK_BIDVOL4,
                KEY_STOCK_OPEN, KEY_STOCK_BID3, KEY_STOCK_BIDVOL3,
                KEY_STOCK_BID2, KEY_STOCK_BIDVOL2, KEY_STOCK_BID1,
                KEY_STOCK_BIDVOL1, KEY_STOCK_ASK1, KEY_STOCK_ASKVOL1,
                KEY_STOCK_ASK2, KEY_STOCK_ASKVOL2, KEY_STOCK_ASK3,
                KEY_STOCK_ASKVOL3, KEY_STOCK_ASK4, KEY_STOCK_ASKVOL4,
                KEY_STOCK_ASK5, KEY_STOCK_ASKVOL5, KEY_STOCK_UPDATE };

        String selection = KEY_STOCK_CODE + " = ?";

        return builder.query(getReadableDatabase(), columns, selection,
                new String[] { symbol }, "", "", "");
    }

    public boolean addStockToWatchList(String code, String name) {
        SQLiteDatabase database = getWritableDatabase();

        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        builder.setTables(TABLE_STOCK);
        Cursor cursor = builder.query(database,
                new String[] { KEY_STOCK_CODE }, KEY_STOCK_CODE + " = ?",
                new String[] { code }, null, null, null);
        boolean isAlreadyInStockTable = cursor.getCount() > 0;
        cursor.close();

        builder = new SQLiteQueryBuilder();
        builder.setTables(TABLE_WATCHLIST);
        cursor = builder.query(database, new String[] { "MAX("
                + KEY_WATCHLIST_ORDER + ")" }, null, null, null, null, null);
        cursor.moveToFirst();
        int maxOrder = cursor.getInt(0);
        cursor.close();

        ContentValues watchListValues = new ContentValues();
        watchListValues.put(KEY_WATCHLIST_CODE, code);
        watchListValues.put(KEY_WATCHLIST_ORDER, maxOrder + 1);

        ContentValues stockDataValues = new ContentValues();
        stockDataValues.put(KEY_STOCK_CODE, code);
        // stockDataValues.put(KEY_STOCK_MARKET, exchange);
        stockDataValues.put(KEY_STOCK_NAME, name);

        database.beginTransaction();
        try {
            database.insert(TABLE_WATCHLIST, null, watchListValues);

            if (!isAlreadyInStockTable) {
                database.insert(TABLE_STOCK, null, stockDataValues);
            }

            database.setTransactionSuccessful();
            return true;
        } catch (Exception e) {
            return false;
        } finally {
            database.endTransaction();
        }
    }

    public Cursor getStockDataTickers() {
        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        builder.setTables(TABLE_STOCK);

        String[] columns = { KEY_ROWID, KEY_STOCK_CODE };

        return builder.query(getReadableDatabase(), columns, null, null, "",
                "", "");
    }

    public void updateStockData(String code, Float changePercentage,
            Float change, Float price, Float open, Float Low, Float High,
            Float yestClose, Float volume, Float turnover, Float bid5,
            Float bidvol5, Float bid4, Float bidvol4, Float bid3,
            Float bidvol3, Float bid2, Float bidvol2, Float bid1,
            Float bidvol1, Float ask1, Float askvol1, Float ask2,
            Float askvol2, Float ask3, Float askvol3, Float ask4,
            Float askvol4, Float ask5, Float askvol5, String update) {
        SQLiteDatabase database = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_STOCK_CHANGE_PERCENTAGE, changePercentage);
        values.put(KEY_STOCK_CHANGE, change);
        values.put(KEY_STOCK_PRICE, price);
        values.put(KEY_STOCK_OPEN, open);
        values.put(KEY_STOCK_LOW, Low);
        values.put(KEY_STOCK_HIGH, High);
        values.put(KEY_STOCK_YESTCLOSE, yestClose);
        values.put(KEY_STOCK_VOLUME, volume);
        values.put(KEY_STOCK_TURNOVER, turnover);
        values.put(KEY_STOCK_BID5, bid5);
        values.put(KEY_STOCK_BIDVOL5, bidvol5);
        values.put(KEY_STOCK_BID4, bid4);
        values.put(KEY_STOCK_BIDVOL4, bidvol4);
        values.put(KEY_STOCK_BID3, bid3);
        values.put(KEY_STOCK_BIDVOL3, bidvol3);
        values.put(KEY_STOCK_BID2, bid2);
        values.put(KEY_STOCK_BIDVOL2, bidvol2);
        values.put(KEY_STOCK_BID1, bid1);
        values.put(KEY_STOCK_BIDVOL1, bidvol1);
        values.put(KEY_STOCK_ASK1, ask1);
        values.put(KEY_STOCK_ASKVOL1, askvol1);
        values.put(KEY_STOCK_ASK2, ask2);
        values.put(KEY_STOCK_ASKVOL2, askvol2);
        values.put(KEY_STOCK_ASK3, ask3);
        values.put(KEY_STOCK_ASKVOL3, askvol3);
        values.put(KEY_STOCK_ASK4, ask4);
        values.put(KEY_STOCK_ASKVOL4, askvol4);
        values.put(KEY_STOCK_ASK5, ask5);
        values.put(KEY_STOCK_ASKVOL5, askvol5);
        values.put(KEY_STOCK_UPDATE, update);

        database.update(TABLE_STOCK, values, KEY_STOCK_CODE + "=?",
                new String[] { code });
    }

    public void updateStockTimeData(String code, long time, Float price,
            Float vol, Float turnover) {
        SQLiteDatabase database = getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_STOCK_TMDATA_REC_CODE, code);
        values.put(KEY_STOCK_TMDATA_REC_TIME, time);
        values.put(KEY_STOCK_TMDATA_REC_PRICE, price);
        values.put(KEY_STOCK_TMDATA_REC_VOL, vol);
        values.put(KEY_STOCK_TMDATA_REC_TURNOVER, turnover);

        // database.insert(TABLE_STOCK_TMDATA, null, values);
        database.beginTransaction();
        try {
            database.insert(TABLE_STOCK_TMDATA, null, values);
            database.setTransactionSuccessful();
            // Log.d(TAG, "dashboard updateStockTimeData successful");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            database.endTransaction();
        }
    }

    public Cursor getStockTimeData(String symbol) {
        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();

        builder.setTables(TABLE_STOCK_TMDATA);
        String[] columns = { KEY_STOCK_TMDATA_REC_CODE,
                KEY_STOCK_TMDATA_REC_TIME, KEY_STOCK_TMDATA_REC_PRICE,
                KEY_STOCK_TMDATA_REC_VOL, KEY_STOCK_TMDATA_REC_TURNOVER };

        String selection = KEY_STOCK_TMDATA_REC_CODE + " = ?";
        String orderby = KEY_STOCK_TMDATA_REC_TIME + " DESC";
        String limit = "300";

        return builder.query(getReadableDatabase(), columns, selection,
                new String[] { symbol }, "", "", orderby, limit);
    }

    public void removeStockFromWatchList(String symbol) {
        getWritableDatabase().delete(TABLE_WATCHLIST,
                KEY_WATCHLIST_CODE + "=?", new String[] { symbol });
        getWritableDatabase().delete(TABLE_STOCK, KEY_STOCK_CODE + "=?",
                new String[] { symbol });
    }

    public void reorderStocks(Stock stock1, Stock stock2) {
        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
        builder.setTables(TABLE_WATCHLIST);

        String[] columns = { KEY_WATCHLIST_CODE, KEY_WATCHLIST_ORDER };

        Cursor cursor = builder.query(getReadableDatabase(), columns,
                KEY_WATCHLIST_CODE + " in (?, ?)",
                new String[] { stock1.getCode(), stock2.getCode() }, null,
                null, null);

        cursor.moveToFirst();
        String symbol1 = cursor.getString(cursor
                .getColumnIndex(KEY_WATCHLIST_CODE));
        int order1 = cursor.getInt(cursor.getColumnIndex(KEY_WATCHLIST_ORDER));

        cursor.moveToNext();
        String symbol2 = cursor.getString(cursor
                .getColumnIndex(KEY_WATCHLIST_CODE));
        int order2 = cursor.getInt(cursor.getColumnIndex(KEY_WATCHLIST_ORDER));

        cursor.close();

        SQLiteDatabase database = getWritableDatabase();
        database.beginTransaction();
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_WATCHLIST_ORDER, order1);
            database.update(TABLE_WATCHLIST, values, KEY_WATCHLIST_CODE + "=?",
                    new String[] { symbol2 });

            values = new ContentValues();
            values.put(KEY_WATCHLIST_ORDER, order2);
            database.update(TABLE_WATCHLIST, values, KEY_WATCHLIST_CODE + "=?",
                    new String[] { symbol1 });

            database.setTransactionSuccessful();
        } finally {
            database.endTransaction();
        }
    }



    private SQLiteDatabase getReadableDatabase() {
        return this.longhornOpenHelper.getReadableDatabase();
    }

    private SQLiteDatabase getWritableDatabase() {
        return this.longhornOpenHelper.getWritableDatabase();
    }
}
